﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Reflection;
using System.Data.OracleClient;

namespace Framework.Library.Core.Utils.Converters
{
    public class SQLTypeConverter
    {
        /// <summary>
        /// [static] Framework.Library.GlobalHelper.ConvertDataReaderToDataSet:
        /// Converts one or more resultsets returned in a SqlDataReader to a DataSet
        /// </summary>
        /// <param name="reader">SqlDataReader</param>
        /// <returns>System.Data.DataSet</returns>
        public static DataSet ToDataSet(SqlDataReader reader)
        {
            var dataSet = new DataSet();
            do
            {
                // Create new data table
                DataTable schemaTable = reader.GetSchemaTable();
                var dataTable = new DataTable();

                if (schemaTable != null)
                {
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dataRow = schemaTable.Rows[i];
                        // Create a column name that is unique in the data table
                        var columnName = (string) dataRow["ColumnName"];
                        // Add the column definition to the data table
                        var column = new DataColumn(columnName, (Type) dataRow["DataType"]);
                        dataTable.Columns.Add(column);
                    }

                    dataSet.Tables.Add(dataTable);

                    while (reader.Read())
                    {
                        DataRow dataRow = dataTable.NewRow();

                        for (int i = 0; i < reader.FieldCount; i++)
                            dataRow[i] = reader.GetValue(i);

                        dataTable.Rows.Add(dataRow);
                    }
                }
                else
                {
                    // No records returned

                    var column = new DataColumn("RowsAffected");
                    dataTable.Columns.Add(column);
                    dataSet.Tables.Add(dataTable);
                    DataRow dataRow = dataTable.NewRow();
                    dataRow[0] = reader.RecordsAffected;
                    dataTable.Rows.Add(dataRow);
                }
            } while (reader.NextResult());
            return dataSet;
        }

        /// <summary>
        /// [static] Framework.Library.GlobalHelper.ConvertDataReaderToDataSet:
        /// Converts one or more resultsets returned in a OracleDataReader to a DataSet
        /// </summary>
        /// <param name="reader">OracleDataReader</param>
        /// <returns>System.Data.DataSet</returns>
        public static DataSet ToDataSet(OracleDataReader reader)
        {
            var dataSet = new DataSet();
            do
            {
                // Create new data table
                DataTable schemaTable = reader.GetSchemaTable();
                var dataTable = new DataTable();

                if (schemaTable != null)
                {
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dataRow = schemaTable.Rows[i];
                        // Create a column name that is unique in the data table
                        var columnName = (string) dataRow["ColumnName"];
                        // Add the column definition to the data table
                        var column = new DataColumn(columnName, (Type) dataRow["DataType"]);
                        dataTable.Columns.Add(column);
                    }

                    dataSet.Tables.Add(dataTable);

                    while (reader.Read())
                    {
                        DataRow dataRow = dataTable.NewRow();

                        for (int i = 0; i < reader.FieldCount; i++)
                            dataRow[i] = reader.GetValue(i);

                        dataTable.Rows.Add(dataRow);
                    }
                }
                else
                {
                    // No records returned

                    var column = new DataColumn("RowsAffected");
                    dataTable.Columns.Add(column);
                    dataSet.Tables.Add(dataTable);
                    DataRow dataRow = dataTable.NewRow();
                    dataRow[0] = reader.RecordsAffected;
                    dataTable.Rows.Add(dataRow);
                }
            } while (reader.NextResult());
            return dataSet;
        }

        /// <summary>
        /// [static] Framework.Library.GlobalHelper.ConvertDataReaderToDataSet:
        /// Converts one or more resultsets returned in a OleDbDataReader to a DataSet
        /// </summary>
        /// <param name="reader">OleDbDataReader</param>
        /// <returns>System.Data.DataSet</returns>
        public static DataSet ToDataSet(OleDbDataReader reader)
        {
            var dataSet = new DataSet();
            do
            {
                // Create new data table
                DataTable schemaTable = reader.GetSchemaTable();
                var dataTable = new DataTable();

                if (schemaTable != null)
                {
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dataRow = schemaTable.Rows[i];
                        // Create a column name that is unique in the data table
                        var columnName = (string) dataRow["ColumnName"];
                        // Add the column definition to the data table
                        var column = new DataColumn(columnName, (Type) dataRow["DataType"]);
                        dataTable.Columns.Add(column);
                    }

                    dataSet.Tables.Add(dataTable);

                    while (reader.Read())
                    {
                        DataRow dataRow = dataTable.NewRow();

                        for (int i = 0; i < reader.FieldCount; i++)
                            dataRow[i] = reader.GetValue(i);

                        dataTable.Rows.Add(dataRow);
                    }
                }
                else
                {
                    // No records returned

                    var column = new DataColumn("RowsAffected");
                    dataTable.Columns.Add(column);
                    dataSet.Tables.Add(dataTable);
                    DataRow dataRow = dataTable.NewRow();
                    dataRow[0] = reader.RecordsAffected;
                    dataTable.Rows.Add(dataRow);
                }
            } while (reader.NextResult());
            return dataSet;
        }

        /// <summary>
        /// [static] Framework.Library.GlobalHelper.ConvertDataReaderToDataSet:
        /// Converts one or more resultsets returned in a OdbcDataReader to a DataSet
        /// </summary>
        /// <param name="reader">OdbcDataReader</param>
        /// <returns>System.Data.DataSet</returns>
        public static DataSet ToDataSet(OdbcDataReader reader)
        {
            var dataSet = new DataSet();
            do
            {
                // Create new data table
                DataTable schemaTable = reader.GetSchemaTable();
                var dataTable = new DataTable();

                if (schemaTable != null)
                {
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dataRow = schemaTable.Rows[i];
                        // Create a column name that is unique in the data table
                        var columnName = (string) dataRow["ColumnName"];
                        // Add the column definition to the data table
                        var column = new DataColumn(columnName, (Type) dataRow["DataType"]);
                        dataTable.Columns.Add(column);
                    }

                    dataSet.Tables.Add(dataTable);

                    while (reader.Read())
                    {
                        DataRow dataRow = dataTable.NewRow();

                        for (int i = 0; i < reader.FieldCount; i++)
                            dataRow[i] = reader.GetValue(i);

                        dataTable.Rows.Add(dataRow);
                    }
                }
                else
                {
                    // No records returned

                    var column = new DataColumn("RowsAffected");
                    dataTable.Columns.Add(column);
                    dataSet.Tables.Add(dataTable);
                    DataRow dataRow = dataTable.NewRow();
                    dataRow[0] = reader.RecordsAffected;
                    dataTable.Rows.Add(dataRow);
                }
            } while (reader.NextResult());
            return dataSet;
        }

        public static DataTable ToDataTable(SqlDataReader reader)
        {
            return ToDataSet(reader).Tables[0];
        }

        public static DataTable ToDataTable(OracleDataReader reader)
        {
            return ToDataSet(reader).Tables[0];
        }

        public static DataTable ToDataTable(OleDbDataReader reader)
        {
            return ToDataSet(reader).Tables[0];
        }

        public static DataTable ToDataTable(OdbcDataReader reader)
        {
            return ToDataSet(reader).Tables[0];
        }

        public static List<T> ToList<T>(SqlDataReader dr)
        {
            var m_List = new List<T>();
            object objTarget = null;
            try
            {
                objTarget = Activator.CreateInstance(typeof (T));
                PropertyInfo[] objProperties = typeof (T).GetProperties();
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    for (int k = 0; k < objProperties.Length; k++)
                    {
                        PropertyInfo property = objProperties[k];
                        if (dr.GetName(i).Equals(property.Name, StringComparison.InvariantCultureIgnoreCase))
                        {
                            if (property.CanWrite)
                            {
                                if (dr[property.Name] != null && dr[property.Name] != DBNull.Value)
                                {
                                    property.SetValue(objTarget,
                                                      Convert.ChangeType(dr[property.Name.ToUpper()],
                                                                         property.PropertyType), null);
                                }
                                else
                                {
                                    property.SetValue(objTarget, null, null);
                                }
                            }
                        }
                    }
                }
                m_List.Add((T) objTarget);
            }
            finally
            {
                dr.Close();
            }
            return m_List;
        }

        public static List<T> ToList<T>(OracleDataReader dr)
        {
            var m_List = new List<T>();
            object objTarget = null;
            try
            {
                objTarget = Activator.CreateInstance(typeof (T));
                PropertyInfo[] objProperties = typeof (T).GetProperties();
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    for (int k = 0; k < objProperties.Length; k++)
                    {
                        PropertyInfo property = objProperties[k];
                        if (dr.GetName(i).Equals(property.Name, StringComparison.InvariantCultureIgnoreCase))
                        {
                            if (property.CanWrite)
                            {
                                if (dr[property.Name] != null && dr[property.Name] != DBNull.Value)
                                {
                                    property.SetValue(objTarget,
                                                      Convert.ChangeType(dr[property.Name.ToUpper()],
                                                                         property.PropertyType), null);
                                }
                                else
                                {
                                    property.SetValue(objTarget, null, null);
                                }
                            }
                        }
                    }
                }
                m_List.Add((T) objTarget);
            }
            finally
            {
                //dr.Close();
            }
            return m_List;
        }

        public static List<T> ToList<T>(OleDbDataReader dr)
        {
            var m_List = new List<T>();
            object objTarget = null;
            try
            {
                objTarget = Activator.CreateInstance(typeof (T));
                PropertyInfo[] objProperties = typeof (T).GetProperties();
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    for (int k = 0; k < objProperties.Length; k++)
                    {
                        PropertyInfo property = objProperties[k];
                        if (dr.GetName(i).Equals(property.Name, StringComparison.InvariantCultureIgnoreCase))
                        {
                            if (property.CanWrite)
                            {
                                if (dr[property.Name] != null)
                                {
                                    property.SetValue(objTarget,
                                                      Convert.ChangeType(dr[property.Name.ToUpper()],
                                                                         property.PropertyType), null);
                                }
                                else
                                {
                                    property.SetValue(objTarget, "", null);
                                }
                            }
                        }
                    }
                }
                m_List.Add((T) objTarget);
            }
            finally
            {
                dr.Close();
            }
            return m_List;
        }

        public static List<T> ToList<T>(OdbcDataReader dr)
        {
            var m_List = new List<T>();
            object objTarget = null;
            try
            {
                objTarget = Activator.CreateInstance(typeof (T));
                PropertyInfo[] objProperties = typeof (T).GetProperties();
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    for (int k = 0; k < objProperties.Length; k++)
                    {
                        PropertyInfo property = objProperties[k];
                        if (dr.GetName(i).Equals(property.Name, StringComparison.InvariantCultureIgnoreCase))
                        {
                            if (property.CanWrite)
                            {
                                if (dr[property.Name] != null)
                                {
                                    property.SetValue(objTarget,
                                                      Convert.ChangeType(dr[property.Name.ToUpper()],
                                                                         property.PropertyType), null);
                                }
                                else
                                {
                                    property.SetValue(objTarget, "", null);
                                }
                            }
                        }
                    }
                }
                m_List.Add((T) objTarget);
            }
            finally
            {
                dr.Close();
            }
            return m_List;
        }
    }
}